盘点 内容 概述 超储／短缺库存 库龄报表 过保（保质期）库存 呆滞物料 畅销物品 / 退货物品 排名 存货估值 概述 不仅仅是盘点。 http://www.chuckboecking.com/covering-manufacturing-gaps-open-source-erp/ 超储／短缺库存 SELECT s.ad_client_id, s.ad_org_id, s.m_warehouse_id, s.m_product_id, p.value, r.Level_Min , r.Level_Max, sum(s.qtyonhand) AS sumqtyonhand , r.level_min-sum(s.qtyonhand) as underbuying , /* 安全库存 --> 短缺 */ sum(s.qtyonhand)-r.level_max as underselling /* 最大库存--> 超储 */ FROM rv_storage s join m_product p on p.m_product_id=s.m_product_id join M_Replenish r on r.m_product_id=s.m_product_id GROUP BY s.m_product_id, s.ad_client_id, s.ad_org_id,s.m_warehouse_id,p.value, r.Level_Min ,r.Level_Max having sum(s.qtyonhand) <= Level_Min or sum(s.qtyonhand) >= Level_Max order by m_product_id 库龄报表 定义：当前时间 －入库时间 SELECT s.ad_client_id, s.ad_org_id, s.m_warehouse_id, s.m_locator_id, s.m_product_id, p.value,p.GuaranteeDays, p.GuaranteeDaysMin,s.DateMaterialPolicy, current_date-s.DateMaterialPolicy as stockdays, /* 入库天数 */ current_date-s.DateMaterialPolicy -p.GuaranteeDays as duedays, /* 按产品保质期计算的过期天数 */ sum(s.qtyonhand) AS sumqtyonhand FROM rv_storage s join m_product p on p.m_product_id=s.m_product_id join m_attributesetinstance a on a.m_attributesetinstance_id=s.m_attributesetinstance_id GROUP BY s.m_product_id, s.ad_client_id, s.ad_org_id,s.m_warehouse_id,s.m_locator_id, s.DateMaterialPolicy ,p.value, p.GuaranteeDays, p.GuaranteeDaysMin ,a.guaranteedate having sum(s.qtyonhand) <> 0 order by stockdays 过保（保质期）库存 SELECT s.ad_client_id, s.ad_org_id, s.m_warehouse_id, s.m_locator_id, s.m_product_id, p.value,p.GuaranteeDays, p.GuaranteeDaysMin, s.DateMaterialPolicy , a.guaranteedate , current_date-s.DateMaterialPolicy as stockdays, /* 入库天数 */ current_date-s.DateMaterialPolicy -p.GuaranteeDays as duedays, /* 按产品保质期计算的过期天数 */ current_date-a.guaranteedate as reallifeleft, /* 按批次保质期计算的过期天数 */ sum(s.qtyonhand) AS sumqtyonhand FROM rv_storage s join m_product p on p.m_product_id=s.m_product_id join m_attributesetinstance a on a.m_attributesetinstance_id=s.m_attributesetinstance_id GROUP BY s.m_product_id, s.ad_client_id, s.ad_org_id,s.m_warehouse_id,s.m_locator_id, s.DateMaterialPolicy ,p.value, p.GuaranteeDays, p.GuaranteeDaysMin ,a.guaranteedate having sum(s.qtyonhand) <> 0 and current_date-s.DateMaterialPolicy -p.GuaranteeDays >0 order by duedays 呆滞物料 定义：库存事务次数 < 1 select t.ad_client_id, t.ad_org_id, t.m_product_id, s.m_locator_id, movementtype, count(t.m_product_id), s.sumqtyonhand from m_transaction t left join rv_storage_per_product s on s.m_product_id=t.m_product_id group by t.ad_client_id, t.ad_org_id,t.m_product_id,movementtype,s.sumqtyonhand,s.m_locator_id having count(t.m_product_id) <2 order by 3 畅销物品 / 退货物品 排名 定义：库存事务出货数量 排名前3，事务类型= C- (客户出货）or C+ (客户退货) select * from ( select t.ad_client_id, t.ad_org_id, t.m_product_id, s.m_locator_id, movementtype, count(t.m_product_id), SUM(t.movementqty) as qty, DENSE_RANK() OVER ( ORDER BY SUM(t.movementqty) ) as rank from m_transaction t left join rv_storage_per_product s on s.m_product_id=t.m_product_id WHERE movementtype = 'C-' group by t.ad_client_id, t.ad_org_id,t.m_product_id,movementtype,s.m_locator_id ) trx where trx.rank < 4 感谢：http://blog.jobbole.com/95110/ 存货估值 睡觉了